package com.jyh.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.jyh.config.BaseAction;
import com.jyh.config.quantity.NewRegister;
import com.jyh.db.entity.JyhManager;
import com.jyh.db.entity.JyhRightRole;
import com.jyh.db.entity.JyhRightRoleExample;
import com.jyh.db.mapper.JyhManagerMapper;
import com.jyh.db.mapper.JyhRightRoleMapper;

@Repository
public class JurisdictionDao extends BaseAction{
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * 获取用户信息
	 * @return
	 */
	public List<JyhManager> getJyhManager() {
		String sql = "select vm.ID,vm.manger,vrr.r_role,vm.is_login from jyh_manager as vm left JOIN jyh_right_role as vrr on vm.role_id = vrr.ID";
		List<JyhManager> list = jdbcTemplate.query(sql, new RowMapper<JyhManager>() {
			
			public JyhManager mapRow(ResultSet rs, int index) throws SQLException {
				JyhManager vm = new JyhManager();
				vm.setId(rs.getInt("ID"));
				vm.setManger(rs.getString("manger"));
				vm.setRoleId(rs.getString("r_role"));
				vm.setIsLogin(rs.getInt("is_login"));
				return vm;
			}
		});
		return list;
	}
	
	/**
	 * 获取角色信息
	 * @return
	 */
	public List<JyhRightRole> getRoleList() {
		
		String sql = "select ID,r_role,r_info,r_parameters  from jyh_right_role";
		List<JyhRightRole> list = jdbcTemplate.query(sql, new RowMapper<JyhRightRole>() {
			
			public JyhRightRole mapRow(ResultSet rs, int index) throws SQLException {
				JyhRightRole vm = new JyhRightRole();
				vm.setId(rs.getInt("ID"));
				vm.setrRole(rs.getString("r_role"));
				vm.setrInfo(rs.getString("r_info"));
				vm.setrParameters( rs.getString("r_parameters"));
				return vm;
			}
		});
		return list;
	}
	
	
	/**
	 * 根据ID获取权限信息
	 * @param id 用户ID
	 * @return
	 */
	public Map<String,Object> getJyhManager(String id) {
		String sql = "select * from jyh_manager where id = '"+id+"'";
		Map<String,Object> map = jdbcTemplate.queryForMap(sql);
		return map;
	}
	/**
	 * 获取权限角色
	 * @return
	 */
	public List<NewRegister> getVaultRightRole() {
		String sql = "select ID,r_role from jyh_right_role";
		List<NewRegister> list = jdbcTemplate.query(sql, new RowMapper<NewRegister>() {
			
			public NewRegister mapRow(ResultSet rs, int index) throws SQLException {
				NewRegister nr = new NewRegister();
				nr.setKeyI(rs.getString("ID"));
				nr.setValueX(rs.getString("r_role"));
				return nr;
			}
		});
		return list;
	}
	
	/**
	 * 修改用户信息
	 * @param id  用户ID
	 * @param password  用户密码
	 * @param isFincing 用户是否可登录
	 * @param roleID 用户登录权限
	 */
	public int getUpdateJyhManager(String id, String password, String isFincing, String roleID,String mobile) {
		String setStr = "";
		if(password!=null){
			setStr+="pwd ='"+password+"',";
		}
		if(isFincing!=null){
 			setStr+="is_login ='"+isFincing+"',";
		}		
		if(roleID!=null){
			setStr+="role_id ='"+roleID+"',";
		}
		if(mobile!=null){
			setStr+="mobile ='"+mobile+"',";
		}
		
		setStr = setStr.substring(0, setStr.length() - 1);
		if(setStr.length()<=0){
			return 0;
		}
		String sql = "update jyh_manager  set "+setStr+" where id="+id;
		int returnInt =jdbcTemplate.update(sql);
		return returnInt;
	}
	
	/**
	 * 验证用户是否已存在
	 * @param name
	 * @return
	 */
	public boolean getSelectNameManager(String name,String mobile) {
		String sql = "select * from jyh_manager where manger = '"+name+"' or mobile='"+mobile+"'";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
		if(list.size()>0){
			return true;
		}else {
			return false;
		}
	}
	
	/**
	 * 验证用户是否已存在
	 * @param name
	 * @return
	 */
	public boolean getSelectNameManager(String mobile) {
		String sql = "select * from jyh_manager where  mobile='"+mobile+"'";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
		if(list.size()>0){
			return true;
		}else {
			return false;
		}
	}
	
	/**
	 * 用户添加
	 * @param userName  用户名
	 * @param password 密码
	 * @param isFincing 是否允许登录
	 * @param roleID 分配权限
	 * @return
	 */
	public int getAddJyhManager(String userName, String password, String isFincing, String roleID,String mobile) {
		String sql = "insert into jyh_manager (manger,pwd,is_login,role_id,mobile) values(?,?,?,?,?)";
		Object[] params = {userName,password,isFincing,roleID,mobile};
		KeyHolder keyHolder = new GeneratedKeyHolder();
		
		jdbcTemplate.update(new PreparedStatementCreator() {
			
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				for (int i = 0; i < params.length; ++i) {
					ps.setString(i + 1, String.valueOf(params[i]));
				}
				return ps;
			}
		}, keyHolder);
		
		// Set the auto id.
		int autoId = keyHolder.getKey().intValue();

		return autoId;
	}
	
	/**
	 * 获取一级导航
	 * @return
	 */
	public List<Map<String, Object>> getVaultNavigate() {
		String sql = "Select * from jyh_navigate order by sort";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
		return list;
	}
	
	/**
	 * 根据一级ID获取二级导航
	 * @param cid
	 * @return
	 */
	public List<Map<String, Object>> getVaultMenu(String Nid) {
		String sql = "Select * from jyh_catalogue where cid="+Nid+" and fid>0 and is_look=1";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
		return list;
	}
	
	public List<Map<String, Object>> getVaultFunction(String Nid) {
		String sql = "Select * from jyh_button where cid="+Nid+" order by ID";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
		return list;
	}
	
	/**
	 * 执行sql语句
	 * @param sql
	 */
	public String getVaultRightRoleIorU(String sql) {
		int returnStr = jdbcTemplate.update(sql);
		return returnStr+"";
	}
	
	/**'
	 * 已有权限数据查询
	 * @param id
	 * @return
	 */
	public Map<String, Object> getVaultRightRoleID(String id) {
		String sql =  "Select * from jyh_right_role where ID="+id ;
		Map<String,Object> list = jdbcTemplate.queryForMap(sql);
		return list;
	}
	
}
